######### Notes ###########

# Files: Death Penalty - Corporal Punishment - Punitiveness,
#        Fear of Crime - Security Behaviour - Satisfaction
# Columns: Varname - Date - Index - Demographic - Poll - Question

######### British Social Attitudes ###########

library(tidyverse)
library(haven) 

setwd("/Users/matteo/Desktop/Prison Polling/Prison Data Internship/Survey Data/Originals/BSAS/")

surveydata <- read_csv("/Users/matteo/Desktop/Prison Polling/Prison Data Internship/Survey Data/Finished/BSA_allrepeatedcrimequestions.csv")

# Create table of variable names and question wording

read_csv("LookUpTable.csv") %>%
  distinct(variable, .keep_all = TRUE) %>%
  select(1,2) %>%
  rename(Varname = variable, Question = values) -> LookUpTable

# Recode variable values so that 1 = punitive/fearful

colnames(surveydata)[4:58]
unique(surveydata$breaklaw)
LookUpTable[LookUpTable$Varname == "wronglaw",]

surveydata %>%
  mutate(spend1 = ifelse(spend1 %in% c('police and prisons','police & prisons'), 'Police & Prisons', spend1),
         spend2 = ifelse(spend2 %in% c('police and prisons','police & prisons'), 'Police & Prisons', spend2),
         stifsent = ifelse(stifsent %in% c("agree", "agree   strongly","agree strongly"), 1,
                           ifelse(is.na(stifsent), NA_integer_, 0)),
         deathapp = ifelse(deathapp %in% c("agree", "agree strongly", "agree   strongly"), 1,
                           ifelse(is.na(deathapp), NA_integer_, 0)),
         wronglaw = ifelse(wronglaw %in% c("agree", "agree strongly", "agree   strongly"), 1,
                           ifelse(is.na(wronglaw), NA_integer_, 0)),
         Trstpol = ifelse(Trstpol %in% c("10 - completely","9","8","7","6","tend to trust it","trust it a great deal"), 1,
                          ifelse(is.na(Trstpol), NA_integer_, 0)),
         Trstct = ifelse(Trstct %in% c("tend to trust it","trust it a great deal"), 1,
                         ifelse(is.na(Trstct), NA_integer_, 0)),
         obeylaw = ifelse(obeylaw %in% c("obey law without exception","always obey law","obey law no exception",
                                         "obey the law without exception","always  obey law","always obey law"), 1,
                          ifelse(is.na(obeylaw), NA_integer_, 0)),
         justice = ifelse(justice %in% c("to let a guilty person go free?","release guilty"), 1,
                          ifelse(is.na(justice), NA_integer_, 0)),
         policrun = ifelse(policrun %in% c("not at all well run", "not very well run","not vry well run","very    well run","not well run"), 1,
                          ifelse(is.na(policrun), NA_integer_, 0)),
         predict1 = ifelse(predict1 %in% c("very likely", "quite likely",
                                           "quite   likely", "very    likely"), 1,
                           ifelse(is.na(predict1), NA_integer_, 0)),
         predict2 = ifelse(predict2 %in% c("very likely", "quite likely",
                                           "quite   likely", "very    likely"), 1,
                           ifelse(is.na(predict2), NA_integer_, 0)),
         predict5 = ifelse(predict5 %in% c("very likely", "quite likely",
                                           "quite   likely", "very    likely"), 1,
                           ifelse(is.na(predict5), NA_integer_, 0)),
         Safe10yr = ifelse(Safe10yr %in% c("agree", "agree strongly"), 1,
                           ifelse(is.na(Safe10yr), NA_integer_, 0)),
         pcnosolc = ifelse(pcnosolc %in% c("agree", "agree strongly", "agree   strongly"), 1,
                           ifelse(is.na(pcnosolc), NA_integer_, 0)),
         idcards = ifelse(idcards %in% c("agree", "agree strongly", "agree   strongly"), 1,
                           ifelse(is.na(idcards), NA_integer_, 0)),
         graffiti = ifelse(graffiti %in% c("fairly common", "very common",
                                           "fairly  common", "very    common"), 1,
                          ifelse(is.na(graffiti), NA_integer_, 0)),
         vandals = ifelse(vandals %in% c("fairly common","very common",
                                         "very    common", "fairly  common"), 1,
                           ifelse(is.na(vandals), NA_integer_, 0)),
         burglary = ifelse(burglary %in% c("fairly common", "very common", "fairly  common", "very    common"), 1,
                           ifelse(is.na(burglary), NA_integer_, 0)),
         attacks = ifelse(attacks %in% c("fairly common","very common","fairly  common", "very    common"), 1,
                          ifelse(is.na(attacks), NA_integer_, 0)),
         vehtheft = ifelse(vehtheft %in% c("fairly common","very common","fairly  common", "very    common"), 1,
                           ifelse(is.na(vehtheft), NA_integer_, 0)),
         stifrsnt = ifelse(stifrsnt %in% c("very effective","quite effective","very    effective", "quite   effective"), 1,
                           ifelse(is.na(stifrsnt), NA_integer_, 0)),
         prisnmor = ifelse(prisnmor %in% c("quite effective","very effective","quite   effective", "very    effective"), 1,
                           ifelse(is.na(prisnmor), NA_integer_, 0)),
         prisnlss = ifelse(prisnlss %in% c("not very effective","notatall effective","quite   effective", "very    effective"), 1,
                           ifelse(is.na(prisnlss), NA_integer_, 0)),
         prisbks = ifelse(prisbks %in% c("probably not","definitely not", "no"), 1,
                           ifelse(is.na(prisbks), NA_integer_, 0)),
         prisvist = ifelse(prisvist %in% c("definitely not","probably not"), 1,
                          ifelse(is.na(prisvist), NA_integer_, 0)),
         priscong = ifelse(priscong %in% c("definitely not", "probably not"), 1,
                           ifelse(is.na(priscong), NA_integer_, 0)),
         prisjob = ifelse(prisjob %in% c("definitely not", "probably not"), 1,
                           ifelse(is.na(prisjob), NA_integer_, 0)),
         prissen1 = ifelse(prissen1 %in% c("strongly agree","agree" ), 1,
                          ifelse(is.na(prissen1), NA_integer_, 0)),
         prissen2 = ifelse(prissen2 %in% c("strongly disagree","disagree" ), 1,
                           ifelse(is.na(prissen2), NA_integer_, 0)),
         prissen3 = ifelse(prissen3 %in% c("strongly disagree","disagree" ), 1,
                           ifelse(is.na(prissen3), NA_integer_, 0)),
         prissen4 = ifelse(prissen4 %in% c("agree","strongly agree" ), 1,
                           ifelse(is.na(prissen4), NA_integer_, 0)),
         prissen5 = ifelse(prissen5 %in% c("strongly disagree","disagree" ), 1,
                           ifelse(is.na(prissen5), NA_integer_, 0)),
         prissen6 = ifelse(prissen6 %in% c("strongly agree","agree" ), 1,
                           ifelse(is.na(prissen6), NA_integer_, 0)),
         victim = ifelse(victim %in% c("yes" ), 1,
                           ifelse(is.na(victim), NA_integer_, 0)),
         vmworry = ifelse(vmworry %in% c("a bit of a worry,","... a big worry,","bit of a worry","big     worry"), 1,
                         ifelse(is.na(vmworry), NA_integer_, 0)),
         pchelp = ifelse(pchelp %in% c("fairly  helpful","very    helpful", "nothing wrong"), 1,
                          ifelse(is.na(pchelp), NA_integer_, 0)),
         pcpolite = ifelse(pcpolite %in% c("fairly  polite", "very    polite"), 1,
                         ifelse(is.na(pcpolite), NA_integer_, 0)),
         cappun1 = ifelse(cappun1 %in% c("favour","in favour"), 1,
                          ifelse(is.na(cappun1), NA_integer_, 0)),
         cappun2 = ifelse(cappun2 %in% c("favour","in favour"), 1,
                          ifelse(is.na(cappun2), NA_integer_, 0)),
         cappun3 = ifelse(cappun3 %in% c("favour","in favour"), 1,
                          ifelse(is.na(cappun3), NA_integer_, 0)),
         breaklaw = ifelse(breaklaw %in% c("no"), 1,
                          ifelse(is.na(breaklaw), NA_integer_, 0)),
         safedark = ifelse(safedark %in% c("a bit   unsafe","very    unsafe", "bit     unsafe"), 1,
                          ifelse(is.na(safedark), NA_integer_, 0)),
         crimin1 = ifelse(crimin1 %in% c("shld def allow", "probably allow"), 1,
                           ifelse(is.na(crimin1), NA_integer_, 0)),
         crimin2 = ifelse(crimin2 %in% c("probably allow", "shld def allow"), 1,
                          ifelse(is.na(crimin2), NA_integer_, 0)),
         crimin3 = ifelse(crimin3 %in% c("shld def allow", "probably allow"), 1,
                          ifelse(is.na(crimin3), NA_integer_, 0)),
         crimin4 = ifelse(crimin4 %in% c("shld def allow", "probably allow"), 1,
                          ifelse(is.na(crimin4), NA_integer_, 0)),
         crimslnt = ifelse(crimslnt %in% c("agree", "agree   strongly"), 1,
                          ifelse(is.na(crimslnt), NA_integer_, 0)),
         vandal = ifelse(vandal %in% c("fairly  common", "very    common"), 1,
                           ifelse(is.na(vandal), NA_integer_, 0)),
         mugging = ifelse(mugging %in% c("fairly  common", "very    common"), 1,
                         ifelse(is.na(mugging), NA_integer_, 0)),
         pcplease = ifelse(pcplease %in% c("yes"), 1,
                          ifelse(is.na(pcplease), NA_integer_, 0)),
         pcsat = ifelse(pcsat %in% c("very    satisfied", "quite   satisfied", "quite   satsfied"), 1,
                          ifelse(is.na(pcsat), NA_integer_, 0)),
         rage = case_when(rage > 15 & rage < 25 ~ "16-24",
                          rage > 24 & rage < 50 ~ "25-49",
                          rage > 49 & rage < 65 ~ "50-64",
                          rage > 64 ~ "65+"),
         Date = paste0(Year, "-01-01"),
         ethnicgp = case_match(ethnicgp,
                               'white' ~ "White",
                               c('black', 'asian', 'other') ~ "Non-white")) -> readforanalysis

# Weighted score for each year by demographic characteristics

readforanalysis %>%
  select(!Year) %>%
  group_by(Date) %>%
  summarise(across(where(is.numeric), \(x) weighted.mean(x, wtfactor, na.rm = TRUE))) %>%
  pivot_longer(-Date) %>%
  drop_na(value) %>%
  filter(name != "wtfactor") %>%
  rename(Index = value, Varname = name) %>%
  mutate(Demographic = "All adults") %>%
  left_join(., readforanalysis %>%
              select(!Year) %>%
              group_by(Date) %>%
              summarise(across(where(is.numeric), ~n())) %>%
              pivot_longer(-Date) %>%
              drop_na(value) %>%
              filter(name != "wtfactor") %>%
              rename(n = value, Varname = name) %>%
              mutate(Demographic = "All adults")) %>%
  bind_rows(readforanalysis %>%
              select(!Year) %>%
              drop_na(rage) %>%
              group_by(rage, Date) %>%
              summarise(across(where(is.numeric), \(x) weighted.mean(x, wtfactor, na.rm = TRUE))) %>%
              pivot_longer(-c(Date, rage)) %>%
              drop_na(value) %>%
              filter(name != "wtfactor") %>%
              rename(Index = value, Varname = name, Demographic = rage) %>%
              left_join(., readforanalysis %>%
                          select(!Year) %>%
                          drop_na(rage) %>%
                          group_by(rage, Date) %>%
                          summarise(across(where(is.numeric), ~n())) %>%
                          pivot_longer(-c(Date, rage)) %>%
                          drop_na(value) %>%
                          filter(name != "wtfactor") %>%
                          rename(n = value, Varname = name, Demographic = rage))) %>%
  bind_rows(readforanalysis %>%
              select(!Year) %>%
              drop_na(rsex) %>%
              group_by(rsex, Date) %>%
              summarise(across(where(is.numeric), \(x) weighted.mean(x, wtfactor, na.rm = TRUE))) %>%
              pivot_longer(-c(Date, rsex)) %>%
              drop_na(value) %>%
              filter(name != "wtfactor") %>%
              rename(Index = value, Varname = name, Demographic = rsex) %>%
              left_join(., readforanalysis %>%
                          select(!Year) %>%
                          drop_na(rsex) %>%
                          group_by(rsex, Date) %>%
                          summarise(across(where(is.numeric), ~n())) %>%
                          pivot_longer(-c(Date, rsex)) %>%
                          drop_na(value) %>%
                          filter(name != "wtfactor") %>%
                          rename(n = value, Varname = name, Demographic = rsex))) %>%
  bind_rows(readforanalysis %>%
              select(!Year) %>%
              drop_na(ethnicgp) %>%
              group_by(ethnicgp, Date) %>%
              summarise(across(where(is.numeric), \(x) weighted.mean(x, wtfactor, na.rm = TRUE))) %>%
              pivot_longer(-c(Date, ethnicgp)) %>%
              drop_na(value) %>%
              filter(name != "wtfactor") %>%
              rename(Index = value, Varname = name, Demographic = ethnicgp) %>%
              left_join(., readforanalysis %>%
                          select(!Year) %>%
                          drop_na(ethnicgp) %>%
                          group_by(ethnicgp, Date) %>%
                          summarise(across(where(is.numeric), ~n())) %>%
                          pivot_longer(-c(Date, ethnicgp)) %>%
                          drop_na(value) %>%
                          filter(name != "wtfactor") %>%
                          rename(n = value, Varname = name, Demographic = ethnicgp))) %>%
  select(Varname, Date, Index, Demographic, n) %>%
  mutate(Poll = "British Social Attitudes") %>%
  left_join(., LookUpTable) %>%
  unnest(cols = Question) -> OutPut

# Update existing files: 
# Death Penalty - Corporal Punishment - Punitiveness - Fear of Crime - Satisfaction

OutPut %>%
  filter(Varname %in% c("obeylaw", "breaklaw", "justice", "crimin1", "crimin2", "crimin3", "crimin4", 
                        "stifsent", "wronglaw", "pcnosolc", "idcards", "prisbks",
                        "crimslnt", "stifrsnt", "prisnmor", "prisnlss","prisvist", "priscong",
                        "prisjob", "prissen1", "prissen2", "prissen3", "prissen4", "prissen5", "prissen6")) %>%
  write.csv(., "/Users/matteo/Desktop/Prison Polling/Dyad Ratio Ready/BSA/Punitiveness.csv", row.names = FALSE)

OutPut %>%
  filter(Varname %in% c("deathapp","cappun1","cappun2","cappun3")) %>%
  write.csv(., "/Users/matteo/Desktop/Prison Polling/Dyad Ratio Ready/BSA/DeathPenalty.csv", row.names = FALSE)

OutPut %>%
  filter(Varname %in% c("predict1","predict2","predict5", "burglary", "victim", "vmworry",
                        "safedark", "vandal", "mugging", "graffiti", "vandals",
                        "attacks", "vehtheft", "Safe10yr")) %>%
  write.csv(., "/Users/matteo/Desktop/Prison Polling/Dyad Ratio Ready/BSA/FearCrime.csv", row.names = FALSE)

OutPut %>%
  filter(Varname %in% c("Trstpol","Trstct", "pchelp", "pcpolite", "pcplease", "pcsat","policrun")) %>%
  write.csv(., "/Users/matteo/Desktop/Prison Polling/Dyad Ratio Ready/BSA/Satisfaction.csv", row.names = FALSE)


# Extract MIP data

readforanalysis %>%
  select(Year, spend1, wtfactor) %>%
  group_by(Year) %>%
  count(spend1, wt = wtfactor) %>%
  group_by(Year) %>%
  mutate(freq = n/sum(n)) %>%
  rename(GovSpend = spend1) %>%
  bind_rows(., readforanalysis %>%
              select(Year, spend2, wtfactor) %>%
              group_by(Year) %>%
              count(spend2, wt = wtfactor) %>%
              group_by(Year) %>%
              mutate(freq = n/sum(n)) %>%
              rename(GovSpend = spend2)) %>%
  filter(GovSpend %in% c('Police & Prisons', 'health', 'education','defence')) %>%
  group_by(Year, GovSpend) %>%
  summarise(freq = sum(freq)) %>%
  mutate(GovSpend = case_match(GovSpend,
    'Police & Prisons' ~ 'Police & Prisons',
    'health' ~ 'Health',
    'education' ~ 'Education',
    'defence' ~ 'Defence')) %>%
  pivot_wider(names_from = GovSpend, values_from = freq, values_fill = NA) %>%
  mutate(Poll = "BSA - Government Spending Priorities") %>%
  write.csv("/Users/matteo/Desktop/Prison Polling/MIP/MIP BSA.csv")
